using System;
using System.Collections.Generic;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using FarPoint.Win;
using FarPoint.Win.Spread.CellType;
using FarPoint.Win.Spread;
using Spread.Common.Features.Sample;
using Spread.Common.DataStore.Models;

namespace GrapeCity.InvoiceSample
{
    public partial class InvoiceControl : BaseSampleControl
    {
        #region Private variables

        private Spread.Common.DataStore.DataService _dataService;
        private Invoice _invoice;
        private Font _headingFontBold;
        private Font _headingFontRegular;
        private ComplexBorder _bottomBorder;
        private Color _themeColor;
        private Color _borderColor;
        private NumberCellType _numCell;
        private NumberCellType _decimalCell;
        private CurrencyCellType _currencyCell;
        private PercentCellType _percentCell;
        private DateTimeCellType _dateTimeCell;

        #endregion

        #region ctor
        public InvoiceControl()
        {
            InitializeComponent();

            InitInvoiceControl();
        }
        #endregion

        #region Private methods
        private void InitInvoiceControl()
        {
            SuspendLayout();

            _decimalCell = new NumberCellType
            {
                DecimalPlaces = 2
            };
            _numCell = new NumberCellType
            {
                DecimalPlaces = 0
            };
            _currencyCell = new CurrencyCellType
            {
                DecimalPlaces = 2,
                CurrencySymbol = "$",
                ShowCurrencySymbol = false,
                ShowSeparator = true
            };
            _percentCell = new PercentCellType
            {
                DecimalPlaces = 3,
                FixedPoint = true,
                LeadingZero = LeadingZero.Yes
            };
            _dateTimeCell = new DateTimeCellType
            {
                DateSeparator = "/"
            };

            _dataService = new Spread.Common.DataStore.DataService();
            List<Customer> customers = _dataService.JsonDataSource.GetAllCustomers();
            _comboCustomer.DropDownStyle = ComboBoxStyle.DropDownList;
            _comboCustomer.DataSource = customers;
            _comboCustomer.DisplayMember = "ID";
            _comboCustomer.ValueMember = "ID";
            _comboCustomer.SelectedIndexChanged += ComboCustomer_SelectedIndexChanged;
            _comboCustomer.Format += ComboCustomer_Format;

            int id = (int)_comboCustomer.SelectedValue;
            List<Invoice> invoices = _dataService.JsonDataSource.GetInvoiceByCustomerID(id);
            _invoiceListBox.DataSource = invoices;
            _invoiceListBox.DisplayMember = "ID";
            _invoiceListBox.ValueMember = "ID";
            _invoiceListBox.CheckOnClick = true;
            _invoiceListBox.SetItemChecked(0, true);

            _headingFontBold = new Font("Segoe UI", 8F, FontStyle.Bold);
            _headingFontRegular = new Font("Segoe UI", 8F, FontStyle.Regular);
            _themeColor = Color.FromArgb(100, 162, 173);
            _borderColor = Color.FromArgb(230, 230, 230);
            _bottomBorder = new ComplexBorder(null, null, null, new ComplexBorderSide(_themeColor, 2));

            _btnOK.Click += BtnOK_Click;

            _fpSpread.ActiveSheetChanged += FpSpread_ActiveSheetChanged;
            _fpSpread.CreateControl();

            GenerateInvoice();

            ResumeLayout();
        }

        private void GenerateInvoice()
        {
            var items = _invoiceListBox.CheckedItems;
            if (items.Count == 0)
            {
                ShowInvoiceSelectionMsgSheet();
                return;
            }
            List<Invoice> invoices = items.Cast<Invoice>().ToList();
            AddInvoiceSheetWithProgress(invoices);
        }

        private void ShowInvoiceSelectionMsgSheet()
        {
            if (_invoiceListBox.CheckedItems.Count == 0)
            {
                _fpSpread.Sheets.Clear();
                _fpSpread.Sheets.Add(new SheetView());
                _fpSpread.Sheets[0].Cells[5, 1].Text = "Select an invoice and click Ok button to generate.";
                _fpSpread.Sheets[0].Cells[5, 1].Font = new Font("Arial", 15, FontStyle.Bold);
                _fpSpread.Sheets[0].Cells[5, 1].VerticalAlignment = CellVerticalAlignment.Center;
                _fpSpread.Sheets[0].Cells[5, 1].HorizontalAlignment = CellHorizontalAlignment.Center;
                _fpSpread.Sheets[0].AddSpanCell(5, 1, 5, 10);
                _btnOK.Enabled = false;
            }
            else
            {
                _btnOK.Enabled = true;
            }
        }

        private async void AddInvoiceSheetWithProgress(List<Invoice> invoices)
        {
            await Task.Run(() => AddInvoiceSheets(invoices));
        }

        private void AddInvoiceSheets(List<Invoice> invoices)
        {
            int count = invoices.Count;
            int sheetCnt = 0;
            if (invoices != null && count > 0)
            {
                if (_fpSpread.InvokeRequired)
                {
                    _fpSpread.Invoke(new MethodInvoker(delegate
                    {
                        _fpSpread.Sheets.Count = 1;
                    }));
                }
                else
                    _fpSpread.Sheets.Count = 1;
                foreach (Invoice invoice in invoices)
                {
                    AddInvoiceSheet(invoice, sheetCnt);
                    if (sheetCnt < count - 1)
                    {
                        if (_fpSpread.InvokeRequired)
                        {
                            _fpSpread.Invoke(new MethodInvoker(delegate
                            {
                                _fpSpread.Sheets.Count += 1;
                            }));
                        }
                        else
                            _fpSpread.Sheets.Count += 1;
                    }
                    ++sheetCnt;
                }
            }
        }

        private void AddInvoiceSheet(Invoice invoice, int index)
        {
            SheetView invoiceSheet = new SheetView
            {
                SheetName = $"{invoice.ID}"
            };

            foreach (SheetView sheet in _fpSpread.Sheets)
            {
                if (sheet.SheetName == invoiceSheet.SheetName)
                    return;
            }
            invoiceSheet.Rows[0].Height = 25;

            int row = 1, col = 0;
            _invoice = _dataService.JsonDataSource.GetInvoice(invoiceSheet.SheetName);
            invoiceSheet.ColumnCount = 10;
            // Hide headers
            invoiceSheet.ColumnHeaderVisible = false;
            invoiceSheet.RowHeaderVisible = false;

            if (_fpSpread.InvokeRequired)
            {
                _fpSpread.Invoke(new MethodInvoker(delegate
                {
                    _fpSpread.Font = _headingFontRegular;
                }));
            }
            else
                _fpSpread.Font = _headingFontRegular;

            // Hide Grid Lines
            invoiceSheet.VerticalGridLine = new GridLine(GridLineType.None);
            invoiceSheet.HorizontalGridLine = new GridLine(GridLineType.None);

            // Formatting for 1st row
            invoiceSheet.Cells[row, col, row, col + 9].ForeColor = Color.White;
            invoiceSheet.Cells[row, col, row, col + 9].BackColor = Color.FromArgb(53, 55, 56);
            invoiceSheet.Cells[row, col, row, col + 9].BackColor = Color.FromArgb(109, 109, 109);
            invoiceSheet.Cells[row, col, row, col + 9].VerticalAlignment = CellVerticalAlignment.Center;
            invoiceSheet.Rows[row].Height = 25;
            invoiceSheet.Rows[row + 1].Height = 30;

            // Column Widths
            invoiceSheet.Columns[col + 0].Width = 15;
            invoiceSheet.Columns[col + 1].Width = 140;
            invoiceSheet.Columns[col + 2, col + 3].Width = 130;
            invoiceSheet.Columns[col + 4].Width = 140;
            invoiceSheet.Columns[col + 5].Width = 80;
            invoiceSheet.Columns[col + 6].Width = 90;
            invoiceSheet.Columns[col + 7].Width = 25;
            invoiceSheet.Columns[col + 8].Width = 70;
            invoiceSheet.Columns[col + 9].Width = 15;

            FillHeaderInfo(invoiceSheet, invoice, row);

            Customer customer = _dataService.JsonDataSource.GetCustomer(invoice.CustomerID);
            FillCustomerInfo(invoiceSheet, customer, row + 2);

            Vehicle vehicle = _dataService.JsonDataSource.GetVehicle(invoice.VehicleID);
            FillVehicleInfo(invoiceSheet, vehicle, row + 2);

            invoiceSheet.Rows[row + 7].Height = 40;

            List<InvoiceServices> invoiceServices = _dataService.JsonDataSource.GetInvoiceServices(invoice.ID);
            row = FillServiceInfo(invoiceSheet, invoiceServices, row + 8);
            int parts_row_start = row + 2;

            invoiceSheet.Rows[row].Height = 30;

            List<InvoiceParts> invoiceParts = _dataService.JsonDataSource.GetInvoiceParts(invoice.ID);

            row = FillPartsInfo(invoiceSheet, invoiceParts, row + 1);

            // Comments Section
            invoiceSheet.AddSpanCell(row, 1, 1, 3);
            invoiceSheet.Cells[row, 1].Font = new Font("Segoe UI", 8F, FontStyle.Bold);
            invoiceSheet.Cells[row, 1].ForeColor = Color.Gray;
            invoiceSheet.Cells[row, 1].VerticalAlignment = CellVerticalAlignment.Center;
            invoiceSheet.Cells[row, 1].Text = "OTHER COMMENTS";

            invoiceSheet.Cells[row, 0, row, 3].Border = new ComplexBorder(null, null, null, new ComplexBorderSide(Color.Gray, 2));

            invoiceSheet.Cells[row + 1, 1, row + 5, 3].Font = new Font("Segoe UI", 8F, FontStyle.Regular);
            invoiceSheet.AddSpanCell(row + 1, 1, 1, 3);
            invoiceSheet.AddSpanCell(row + 2, 1, 1, 3);
            invoiceSheet.AddSpanCell(row + 3, 1, 1, 3);
            invoiceSheet.AddSpanCell(row + 5, 1, 1, 3);

            invoiceSheet.Cells[row + 1, 1].Text = "1. Total payment due in 30 days.";
            invoiceSheet.Cells[row + 2, 1].Text = "2. Please include the invoice number on your check.";
            invoiceSheet.Cells[row + 3, 1].Text = "3. Please mail your check to the address listed above.";

            invoiceSheet.Rows[row + 5].Height = 25;
            invoiceSheet.Cells[row + 5, 1].Font = new Font("Segoe UI", 10, FontStyle.Bold);
            invoiceSheet.Cells[row + 5, 1].ForeColor = _themeColor;
            invoiceSheet.Cells[row + 5, 1].Text = "Thank You For Your Business!";

            // Total Amount Section
            invoiceSheet.Cells[row - 2, 1, row - 2, invoiceSheet.ColumnCount - 2].Border = _bottomBorder;
            invoiceSheet.Cells[row - 1, 6, row + 5, 8].VerticalAlignment = CellVerticalAlignment.Center;
            invoiceSheet.Cells[row - 1, 6, row + 5, 7].Font = _headingFontBold;
            invoiceSheet.Cells[row - 1, 6, row + 5, 6].ForeColor = _themeColor;
            invoiceSheet.Cells[row - 1, 6].Text = "SUBTOTAL";
            invoiceSheet.Cells[row - 1, 7].HorizontalAlignment = CellHorizontalAlignment.Center;
            invoiceSheet.Cells[row - 1, 7].Font = new Font(invoiceSheet.Cells[row - 1, 7].Font, FontStyle.Bold);
            invoiceSheet.Cells[row - 1, 7].Text = "$";
            invoiceSheet.Cells[row - 1, 8].CellType = _currencyCell;
            invoiceSheet.Cells[row - 1, 8].Font = new Font("Segoe UI", 9, FontStyle.Bold);
            invoiceSheet.Cells[row - 1, 6, row - 1, 8].BackColor = Color.LightGray;

            string formula = $"H{parts_row_start + 1}";
            for (int i = 2; i <= invoiceParts.Count; ++i)
                formula += $" + H{parts_row_start + i}";
            invoiceSheet.AddCustomName("PartsSubTotal", $"${(char)('A' + 8)}${row - 1 + 1}", 0, 0, true, string.Empty);
            invoiceSheet.Cells[row - 1, 8].Tag = "PartsSubTotal";
            invoiceSheet.Cells[row - 1, 8].Formula = formula;
            invoiceSheet.Cells[row - 1, 8].Locked = true;
            invoice.TotalPartsAmount = Convert.ToDecimal(invoiceSheet.Cells[row - 1, 8].Value);
            invoiceSheet.AddCustomName("PartsTaxRate", $"${(char)('A' + 8)}${row + 1}", 0, 0, true, string.Empty);
            invoiceSheet.Cells[row, 7].Tag = "PartsTaxRate";
            invoiceSheet.Cells[row, 7].Value = invoice.TaxRateParts;

            invoiceSheet.Cells[row, 6].Text = "TAX RATE";
            invoiceSheet.AddSpanCell(row, 7, 1, 2);
            invoiceSheet.Cells[row, 7].Font = new Font("Segoe UI", 8F, FontStyle.Regular);
            invoiceSheet.Cells[row, 7].CellType = _percentCell;


            invoiceSheet.Cells[row + 2, 6].Text = "TOTAL SERVICES";
            invoiceSheet.Cells[row + 2, 7].Font = new Font(invoiceSheet.Cells[row + 2, 7].Font, FontStyle.Bold);
            invoiceSheet.Cells[row + 2, 7].HorizontalAlignment = CellHorizontalAlignment.Center;
            invoiceSheet.Cells[row + 2, 7].Text = "$";
            invoiceSheet.Cells[row + 2, 8, row + 5, 8].CellType = _currencyCell;
            invoiceSheet.Cells[row + 2, 8].Formula = invoiceSheet.Cells["ServiceSubTotal"].Formula;

            invoiceSheet.Cells[row + 2, 8].Font = new Font("Segoe UI", 9, FontStyle.Bold);
            invoiceSheet.Cells[row + 2, 8].Locked = true;
            invoiceSheet.Cells[row + 2, 8].Tag = "TotalServiceAmount";
            invoiceSheet.AddCustomName("TotalServiceAmount", $"${(char)('A' + 8)}${row + 2 + 1}", 0, 0, true, string.Empty);

            invoiceSheet.Cells[row + 3, 6].Text = "TOTAL PARTS";
            invoiceSheet.Cells[row + 3, 7].Text = "$";
            invoiceSheet.Cells[row + 3, 7].HorizontalAlignment = CellHorizontalAlignment.Center;
            invoiceSheet.Cells[row + 3, 7].Font = new Font(invoiceSheet.Cells[row + 3, 7].Font, FontStyle.Bold);
            invoiceSheet.Cells[row + 3, 8].Formula = invoiceSheet.Cells["PartsSubTotal"].Formula;

            invoiceSheet.Cells[row + 3, 8].Font = new Font("Segoe UI", 9, FontStyle.Bold);
            invoiceSheet.Cells[row + 3, 8].Locked = true;
            invoiceSheet.Cells[row + 3, 8].Tag = "TotalPartsAmount";
            invoiceSheet.AddCustomName("TotalPartsAmount", $"${(char)('A' + 8)}${row + 3 + 1}", 0, 0, true, string.Empty);

            invoiceSheet.Cells[row + 4, 6].Text = "TOTAL TAX";
            invoiceSheet.Cells[row + 4, 7].HorizontalAlignment = CellHorizontalAlignment.Center;
            invoiceSheet.Cells[row + 4, 7].Font = new Font(invoiceSheet.Cells[row + 4, 7].Font, FontStyle.Bold);
            invoiceSheet.Cells[row + 4, 7].Text = "$";
            invoiceSheet.Cells[row + 4, 8].Formula = $"({invoiceSheet.SheetName}!TotalPartsAmount * {invoiceSheet.SheetName}!PartsTaxRate) + " +
                $"({invoiceSheet.SheetName}!TotalServiceAmount * {invoiceSheet.SheetName}!ServiceTaxRate)";

            invoiceSheet.Cells[row + 4, 8].Font = new Font("Segoe UI", 9, FontStyle.Bold);
            invoiceSheet.Cells[row + 4, 8].Locked = true;
            invoiceSheet.Cells[row + 4, 8].Tag = "TotalTax";
            invoiceSheet.AddCustomName("TotalTax", $"{invoiceSheet.SheetName}!${(char)('A' + 8)}${row + 4 + 1}", 0, 0, true, string.Empty);
            invoiceSheet.Cells[row + 4, 6, row + 4, 9].Border = new ComplexBorder(null, null, null, new ComplexBorderSide(Color.FromArgb(176, 176, 176), 1));

            invoiceSheet.Cells[row + 5, 6, row + 5, 8].BackColor = Color.LightGray;
            invoiceSheet.Cells[row + 5, 6, row + 5, 8].Font = new Font("Segoe UI", 10F, FontStyle.Bold);
            invoiceSheet.Cells[row + 5, 6].Text = "TOTAL";
            invoiceSheet.Cells[row + 5, 8].Locked = true;
            invoiceSheet.Cells[row + 5, 8].Formula = $"{invoiceSheet.SheetName}!TotalPartsAmount + {invoiceSheet.SheetName}!TotalServiceAmount + " +
                $"{invoiceSheet.SheetName}!TotalTax";
            invoiceSheet.Cells[row + 5, 8].Formula = $"{invoiceSheet.SheetName}!$I${invoiceSheet.Cells["TotalPartsAmount"].Row.Index + 1} + " +
                $"{invoiceSheet.SheetName}!$I${invoiceSheet.Cells["TotalServiceAmount"].Row.Index + 1} + " +
                $"{invoiceSheet.SheetName}!$I${invoiceSheet.Cells["TotalTax"].Row.Index + 1}";
            invoiceSheet.Cells[row + 5, 8].Tag = "TotalAmount";
            invoice.TotalAmount = Convert.ToDecimal(invoiceSheet.Cells["TotalAmount"].Value);

            invoiceSheet.Cells[row + 5, 6, row + 5, 9].Border = new ComplexBorder(null, new ComplexBorderSide(Color.FromArgb(176, 176, 176), 1),
                null, null);

            invoiceSheet.Cells[row + 5, 7].Value = _currencyCell.CurrencySymbol;
            invoiceSheet.Cells[row + 5, 7].HorizontalAlignment = CellHorizontalAlignment.Center;

            invoiceSheet.Rows[row + 6].Height = 25;
            invoiceSheet.AddSpanCell(row + 7, 6, 1, 3);
            invoiceSheet.Cells[row + 7, 6].Font = new Font("Segoe UI", 8F, FontStyle.Regular);
            invoiceSheet.Cells[row + 7, 6].HorizontalAlignment = CellHorizontalAlignment.Center;
            invoiceSheet.Cells[row + 7, 6].Text = "Make all checks payable to:";

            invoiceSheet.AddSpanCell(row + 8, 6, 1, 3);
            invoiceSheet.Cells[row + 8, 6].Font = new Font("Segoe UI", 8F, FontStyle.Bold);
            invoiceSheet.Cells[row + 8, 6].ForeColor = _themeColor;
            invoiceSheet.Cells[row + 8, 6].HorizontalAlignment = CellHorizontalAlignment.Center;
            invoiceSheet.Cells[row + 8, 6].Text = "Your Company Name";

            invoiceSheet.Rows[row + 9].Height = 25;
            invoiceSheet.Rows.Remove(row + 10, invoiceSheet.Rows.Count - row - 10);

            // Margins
            invoiceSheet.Columns.Add(0, 1);
            invoiceSheet.Columns[0].Width = 25;
            invoiceSheet.Columns.Add(invoiceSheet.ColumnCount, 1);
            invoiceSheet.Columns[invoiceSheet.ColumnCount - 1].Width = 25;

            // Margin Borders
            invoiceSheet.Cells[1, 1, row + 8, 1].Border = new ComplexBorder(new ComplexBorderSide(Color.Gray, 2), null, null, null);
            invoiceSheet.Cells[1, invoiceSheet.ColumnCount - 2, row + 8, invoiceSheet.ColumnCount - 2].Border = new ComplexBorder(null, null,
                new ComplexBorderSide(Color.Gray, 2), null);
            invoiceSheet.Cells[row + 9, 2, row + 9, invoiceSheet.ColumnCount - 3].Border = new ComplexBorder(null, null, null,
                new ComplexBorderSide(Color.Gray, 2));

            // Corner Cells
            invoiceSheet.Cells[row + 9, invoiceSheet.ColumnCount - 2].Border = new ComplexBorder(null, null, new ComplexBorderSide(Color.Gray, 2),
                new ComplexBorderSide(Color.Gray, 2));
            invoiceSheet.Cells[row + 9, 1].Border = new ComplexBorder(new ComplexBorderSide(Color.Gray, 2), null, null,
                new ComplexBorderSide(Color.Gray, 2));

            if (_fpSpread.InvokeRequired)
            {
                _fpSpread.Invoke(new MethodInvoker(() =>
                {
                    _fpSpread.Sheets[index] = invoiceSheet;
                }));
            }
            else
                _fpSpread.Sheets[index] = invoiceSheet;

            UpdateDataSource(invoice);
        }

        private void UpdateDataSource(Invoice invoice)
        {
            _dataService.JsonDataSource.UpdateInvoice(invoice);
        }

        private void FillHeaderInfo(SheetView sheet, Invoice invoice, int start_row)
        {
            // Invoice # Label Cell
            sheet.Cells[start_row + 0, 1].Text = "INVOICE #";
            sheet.Cells[start_row + 0, 1].HorizontalAlignment = CellHorizontalAlignment.Right;
            sheet.Cells[start_row + 0, 1].Locked = true;

            // Invoice # Cell
            sheet.Cells[start_row + 0, 2].TextIndent = 20;
            sheet.Cells[start_row + 0, 2].HorizontalAlignment = CellHorizontalAlignment.Left;
            sheet.Cells[start_row + 0, 2].Text = invoice.ID;

            // Date Label Cell
            sheet.Cells[start_row + 0, 6].Text = "DATE";
            sheet.Cells[start_row + 0, 6].HorizontalAlignment = CellHorizontalAlignment.Left;
            sheet.Cells[start_row + 0, 6].TextIndent = 20;

            // Date Cell
            sheet.Cells[start_row + 0, 7].HorizontalAlignment = CellHorizontalAlignment.Left;
            sheet.Cells[start_row + 0, 7].CellType = _dateTimeCell;
            sheet.Cells[start_row + 0, 7].Locked = true;
            sheet.AddSpanCell(start_row + 0, 7, 1, 2);

            DateTime currDate = DateTime.Now;
            Random random = new Random(currDate.Millisecond);
            DateTime createdOn = currDate.AddDays(-random.Next(0, 5));
            sheet.Cells[start_row + 0, 7].Value = createdOn;
            invoice.CreatedOn = createdOn;
        }
        private void FillCustomerInfo(SheetView sheet, Customer customer, int start_row)
        {
            // Customer Info Block Cell
            ImageCellType imageCell = new ImageCellType
            {
                Style = RenderStyle.StretchAndScale
            };
            sheet.AddSpanCell(start_row, 1, 5, 1);
            sheet.Cells[start_row, 1].CellType = imageCell;
            int width = (int)sheet.Columns[1].Width;
            int height = (int)(sheet.Rows[start_row].Height * 5);
            sheet.Cells[start_row, 1].Value = Image.FromFile(@"Images/" + customer.ID + ".png");

            // Customer Name Cell
            sheet.AddSpanCell(start_row, 2, 1, 2);
            sheet.Cells[start_row, 2].TextIndent = 15;

            // Customer Street Address Cell
            sheet.AddSpanCell(start_row + 1, 2, 1, 2);
            sheet.Cells[start_row + 1, 2].TextIndent = 15;

            // Customer City, State, Zip Cell
            sheet.AddSpanCell(start_row + 2, 2, 1, 2);
            sheet.Cells[start_row + 2, 2].TextIndent = 15;

            // Customer Phone Cell
            sheet.AddSpanCell(start_row + 3, 2, 1, 2);
            sheet.Cells[start_row + 3, 2].TextIndent = 15;

            sheet.Cells[start_row, 2].Text = customer.Name;
            sheet.Cells[start_row + 1, 2].Text = customer.StreetAddress;
            sheet.Cells[start_row + 2, 2].Text = $"{customer.City}, {customer.State} {customer.Zipcode}";
            MaskCellType maskCell = new MaskCellType
            {
                Mask = @"\Phone: (###) ###-####"
            };
            sheet.Cells[start_row + 3, 2].CellType = maskCell;
            sheet.Cells[start_row + 3, 2].Value = customer.Phone;
        }
        private void FillVehicleInfo(SheetView sheet, Vehicle vehicle, int start_row)
        {
            // Vehicle Info Block Cell
            ImageCellType imageCell = new ImageCellType
            {
                Style = RenderStyle.StretchAndScale
            };

            sheet.AddSpanCell(start_row, 4, 5, 1);
            sheet.Cells[start_row, 4].CellType = imageCell;
            Random random = new Random();
            sheet.Cells[start_row, 4].Value = Image.FromFile(@"Images/" + vehicle.Color + ".png");

            // Vehicle Info Label Cells
            sheet.Cells[start_row, 5].Text = "Year:";
            sheet.Cells[start_row + 1, 5].Text = "Make:";
            sheet.Cells[start_row + 2, 5].Text = "Model:";
            sheet.Cells[start_row + 3, 5].Text = "Color:";
            sheet.Cells[start_row + 4, 5].Text = "Mileage:";
            sheet.Cells[start_row, 5, start_row + 4, 5].TextIndent = 15;


            sheet.Cells[start_row, 6].Text = vehicle.Year.ToString();
            sheet.Cells[start_row + 1, 6].Text = vehicle.Make;
            sheet.Cells[start_row + 2, 6].Text = vehicle.Model;
            sheet.Cells[start_row + 3, 6].Text = vehicle.Color;
            sheet.Cells[start_row + 4, 6].Text = vehicle.Mileage.ToString();
            sheet.Cells[start_row, 6].HorizontalAlignment = CellHorizontalAlignment.Left;
            sheet.Cells[start_row + 4, 6].HorizontalAlignment = CellHorizontalAlignment.Left;

        }
        private int FillServiceInfo(SheetView sheet, List<InvoiceServices> invoiceServices, int start_row)
        {
            // Services Header
            sheet.AddSpanCell(start_row, 1, 1, 6);
            sheet.Cells[start_row, 1, start_row, sheet.ColumnCount - 2].Border = _bottomBorder;
            sheet.Cells[start_row, 1, start_row, 8].ForeColor = _themeColor;
            sheet.Cells[start_row, 1, start_row, 8].Font = _headingFontBold;
            sheet.Cells[start_row, 1].Text = "SERVICES PERFORMED";
            sheet.Cells[start_row, 1].VerticalAlignment = CellVerticalAlignment.Center;

            sheet.Cells[start_row, 8].Text = "AMOUNT";
            sheet.Cells[start_row, 8].HorizontalAlignment = CellHorizontalAlignment.Right;
            sheet.Cells[start_row, 8].VerticalAlignment = CellVerticalAlignment.Center;

            // Service Rows

            int i = 1;
            for (; i <= invoiceServices.Count; ++i)
            {
                sheet.AddSpanCell(start_row + i, 1, 1, 6);
                sheet.Cells[start_row + i, 7].CellType = _decimalCell;
                sheet.Cells[start_row + i, 1].Text = invoiceServices[i - 1].Description;
                sheet.Cells[start_row + i, 1].Border = new ComplexBorder(null, null, new ComplexBorderSide(_borderColor, 2), new ComplexBorderSide(_borderColor, 2));
                sheet.AddSpanCell(start_row + i, 7, 1, 2);
                sheet.Cells[start_row + i, 7].Value = invoiceServices[i - 1].ServiceAmount;
                sheet.Cells[start_row + i, 7].Border = new ComplexBorder(null, null, null, new ComplexBorderSide(_borderColor, 2));
            }

            for (int j = start_row + i; j < start_row + i + 2; ++j)
            {
                sheet.AddSpanCell(j, 1, 1, 6);
                sheet.Rows[j].Locked = true;
                sheet.Cells[j, 1].Border = new ComplexBorder(null, null, new ComplexBorderSide(_borderColor, 2), new ComplexBorderSide(_borderColor, 2));
                sheet.AddSpanCell(j, 7, 1, 2);
                sheet.Cells[j, 7].Border = new ComplexBorder(null, null, null, new ComplexBorderSide(_borderColor, 2));
            }
            sheet.AddSpanCell(start_row + i + 2, 1, 1, 8);
            sheet.Cells[start_row + i + 2, 1].Border = new ComplexBorder(null, null, null, new ComplexBorderSide(_themeColor, 2));
            sheet.Rows[start_row + i + 2].Locked = true;
            // Service Total Section
            sheet.Cells[start_row + i + 2, 1, start_row + i + 2, sheet.ColumnCount - 2].Border = _bottomBorder;
            sheet.Cells[start_row + i + 3, 6, start_row + i + 4, 6].Font = _headingFontBold;
            sheet.Cells[start_row + i + 3, 6, start_row + i + 4, 6].ForeColor = _themeColor;
            sheet.Cells[start_row + i + 3, 6].Text = "SUBTOTAL";
            sheet.Cells[start_row + i + 4, 6].Text = "TAX RATE";

            sheet.Cells[start_row + i + 3, 7, start_row + i + 3, 8].Font = _headingFontBold;
            sheet.Cells[start_row + i + 3, 7].Value = _currencyCell.CurrencySymbol;
            sheet.Cells[start_row + i + 3, 7].HorizontalAlignment = CellHorizontalAlignment.Center;
            sheet.Cells[start_row + i + 3, 8].CellType = _currencyCell;
            sheet.Cells[start_row + i + 3, 6, start_row + i + 3, 8].BackColor = Color.LightGray;

            string formula = $"H{start_row + 2}";
            for (int j = 2; j <= invoiceServices.Count; ++j)
                formula += $" + H{start_row + 1 + j}";

            sheet.Cells[start_row + i + 3, 8].Tag = "ServiceSubTotal";
            sheet.Cells[start_row + i + 3, 8].Font = new Font("Segoe UI", 9, FontStyle.Bold);
            sheet.Cells[start_row + i + 3, 8].Formula = formula;
            sheet.Cells[start_row + i + 3, 8].Locked = true;
            sheet.AddCustomName("ServiceSubTotal", $"${(char)('A' + 8)}${start_row + i + 3 + 1}", 0, 0, true, string.Empty);
            _invoice.TotalServiceAmount = Convert.ToDecimal(sheet.Cells[start_row + i + 3, 8].Value);
            sheet.AddSpanCell(start_row + i + 4, 7, 1, 2);
            sheet.Cells[start_row + i + 4, 7].Tag = "ServiceTaxRate";
            sheet.Cells[start_row + i + 4, 7].Value = _invoice.TaxRateServices;
            sheet.AddCustomName("ServiceTaxRate", $"${(char)('A' + 8)}${start_row + i + 4 + 1}", 0, 0, true, string.Empty);

            sheet.Cells[start_row + i + 4, 7].CellType = _percentCell;
            return start_row + i + 6;
        }
        private int FillPartsInfo(SheetView sheet, List<InvoiceParts> invoiceParts, int start_row)
        {
            // Parts Section
            sheet.Rows[start_row].Font = _headingFontBold;
            sheet.Rows[start_row].ForeColor = _themeColor;
            sheet.Cells[start_row, 1, start_row, sheet.ColumnCount - 2].Border = _bottomBorder;

            sheet.AddSpanCell(start_row, 1, 1, 3);
            sheet.Cells[start_row, 1].Text = "PART NAME";
            sheet.Cells[start_row, 4].Text = "PART #";
            sheet.Cells[start_row, 5].Text = "QTY";
            sheet.Cells[start_row, 6].Text = "UNIT PRICE";
            sheet.Cells[start_row, 4, start_row, 6].HorizontalAlignment = CellHorizontalAlignment.Center;


            // Part Amount Header Cell
            sheet.Cells[start_row, 8].HorizontalAlignment = CellHorizontalAlignment.Right;
            sheet.Cells[start_row, 8].Text = "AMOUNT";

            int i = 1;
            for (; i <= invoiceParts.Count; ++i)
            {
                sheet.AddSpanCell(start_row + i, 1, 1, 3);
                sheet.Cells[start_row + i, 6].CellType = _numCell;
                sheet.Cells[start_row + i, 7].CellType = _currencyCell;
                sheet.Cells[start_row + i, 4, start_row + i, 6].HorizontalAlignment = CellHorizontalAlignment.Center;

                Parts part = _dataService.JsonDataSource.GetAllParts().FirstOrDefault(p => p.ID == invoiceParts[i - 1].PartsID);
                sheet.Cells[start_row + i, 1].Text = part.Name;
                sheet.Cells[start_row + i, 4].Value = part.ID;
                sheet.Cells[start_row + i, 5].CellType = _numCell;
                sheet.Cells[start_row + i, 5].Value = invoiceParts[i - 1].Quantity;
                sheet.Cells[start_row + i, 6].Value = part.UnitPrice;
                sheet.Cells[start_row + i, 1, start_row + i, 6].Border = new ComplexBorder(null, null, new ComplexBorderSide(_borderColor, 2), new ComplexBorderSide(_borderColor, 2));

                sheet.Cells[start_row + i, 0].Border = new ComplexBorder(null, null, null, new ComplexBorderSide(_borderColor, 2));
                sheet.Cells[start_row + i, 7, start_row + i, 9].Border = new ComplexBorder(null, null, null, new ComplexBorderSide(_borderColor, 2));

                sheet.AddSpanCell(start_row + i, 7, 1, 2);
                sheet.Cells[start_row + i, 7].Formula = $"F{start_row + i + 1} * G{start_row + i + 1}";
                sheet.Cells[start_row + i, 7].Border = new ComplexBorder(null, null, null, new ComplexBorderSide(_borderColor, 2));
                sheet.Cells[start_row + i, 7].Locked = true;
            }

            for (int j = 0; j < 2; ++j)
            {
                sheet.AddSpanCell(start_row + i + j, 1, 1, 3);
                sheet.Cells[start_row + i + j, 1].Border = new ComplexBorder(null, null, new ComplexBorderSide(_borderColor, 2), new ComplexBorderSide(_borderColor, 2));
                sheet.Cells[start_row + i + j, 3, start_row + i + j, 6].Border = new ComplexBorder(null, null, new ComplexBorderSide(_borderColor, 2), new ComplexBorderSide(_borderColor, 2));
                sheet.Rows[start_row + i + j].Locked = true;
                sheet.AddSpanCell(start_row + i + j, 7, 1, 2);
                sheet.Cells[start_row + i + j, 7, start_row + i + j, 9].Border = new ComplexBorder(null, null, null, new ComplexBorderSide(_borderColor, 2));

                sheet.Cells[start_row + i + j, 8].Text = "-";
                sheet.Cells[start_row + i + j, 8].TextIndent = 90;
            }
            sheet.AddSpanCell(start_row + i + 2, 1, 1, 3);
            sheet.AddSpanCell(start_row + i + 2, 7, 1, 2);

            sheet.Cells[start_row + i + 2, 8].Text = "-";
            sheet.Cells[start_row + i + 2, 8].TextIndent = 90;
            sheet.Cells[start_row + i + 2, 3, start_row + i + 2, 6].Border = new ComplexBorder(null, null, new ComplexBorderSide(_borderColor, 2), new ComplexBorderSide(_themeColor, 2));
            sheet.Rows[start_row + i + 2].Locked = true;
            return start_row + i + 4;
        }
        #endregion

        #region Event Handlers
        private void FpSpread_ActiveSheetChanged(object sender, EventArgs e)
        {
            string name = string.Empty;
            if (_fpSpread.ActiveSheet == null)
                return;
            _fpSpread.BeginInvoke(new MethodInvoker(delegate
            {
                name = _fpSpread.ActiveSheet.SheetName;
            }));
            _invoice = _dataService.JsonDataSource.GetInvoice(name);
        }

        private void BtnOK_Click(object sender, EventArgs e)
        {
            _fpSpread.SuspendLayout();
            GenerateInvoice();
            _fpSpread.ResumeLayout(true);
        }

        private void ComboCustomer_SelectedIndexChanged(object sender, EventArgs e)
        {
            while (_invoiceListBox.CheckedItems.Count > 0)
                _invoiceListBox.SetItemCheckState(_invoiceListBox.Items.IndexOf(_invoiceListBox.CheckedItems[0]), CheckState.Unchecked);

            _fpSpread.SuspendLayout();
            int id = (int)_comboCustomer.SelectedValue;
            List<Invoice> invoices = _dataService.JsonDataSource.GetInvoiceByCustomerID(id);
            _invoiceListBox.DataSource = invoices;
            _invoiceListBox.DisplayMember = "ID";
            _invoiceListBox.ValueMember = "ID";
            _invoiceListBox.SetItemCheckState(0, CheckState.Checked);
            GenerateInvoice();
            _fpSpread.ResumeLayout(true);
        }

        private void ComboCustomer_Format(object sender, ListControlConvertEventArgs e)
        {
            Customer customer = e.ListItem as Customer;
            string value;
            if (customer.ID == -1)
                value = customer.Name;
            else
                value = $"ID: {customer.ID}, {customer.Name}";
            e.Value = value;
        }
        #endregion
    }
}


//
//InvoiceControl.Designer.cs
//


namespace GrapeCity.InvoiceSample
{
    partial class InvoiceControl
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            FarPoint.Win.Spread.FlatScrollBarRenderer flatScrollBarRenderer3 = new FarPoint.Win.Spread.FlatScrollBarRenderer();
            FarPoint.Win.Spread.FlatScrollBarRenderer flatScrollBarRenderer4 = new FarPoint.Win.Spread.FlatScrollBarRenderer();
            this._fpSpread = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None);
            this._fpSpread_Sheet1 = new FarPoint.Win.Spread.SheetView();
            this._labelCustomer = new System.Windows.Forms.Label();
            this._comboCustomer = new System.Windows.Forms.ComboBox();
            this._labelInvoice = new System.Windows.Forms.Label();
            this._invoiceListBox = new System.Windows.Forms.CheckedListBox();
            this._btnOK = new System.Windows.Forms.Button();
            this.pnlMain.SuspendLayout();
            this.pnlControls.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this._fpSpread)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this._fpSpread_Sheet1)).BeginInit();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this._fpSpread);
            // 
            // pnlControls
            // 
            this.pnlControls.Controls.Add(this._btnOK);
            this.pnlControls.Controls.Add(this._invoiceListBox);
            this.pnlControls.Controls.Add(this._labelInvoice);
            this.pnlControls.Controls.Add(this._comboCustomer);
            this.pnlControls.Controls.Add(this._labelCustomer);
            // 
            // _fpSpread
            // 
            this._fpSpread.AccessibleDescription = "";
            this._fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this._fpSpread.HorizontalScrollBar.Buttons = new FarPoint.Win.Spread.FpScrollBarButtonCollection("BackwardLineButton,ThumbTrack,ForwardLineButton");
            this._fpSpread.HorizontalScrollBar.Name = "";
            flatScrollBarRenderer3.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(255)))), ((int)(((byte)(255)))), ((int)(((byte)(255)))));
            this._fpSpread.HorizontalScrollBar.Renderer = flatScrollBarRenderer3;
            this._fpSpread.Location = new System.Drawing.Point(0, 0);
            this._fpSpread.Name = "_fpSpread";
            this._fpSpread.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
            this._fpSpread_Sheet1});
            this._fpSpread.Size = new System.Drawing.Size(477, 487);
            this._fpSpread.Skin = FarPoint.Win.Spread.DefaultSpreadSkins.Office2016White;
            this._fpSpread.StatusBarVisible = true;
            this._fpSpread.TabIndex = 0;
            this._fpSpread.TabStripPolicy = FarPoint.Win.Spread.TabStripPolicy.Always;
            this._fpSpread.VerticalScrollBar.Buttons = new FarPoint.Win.Spread.FpScrollBarButtonCollection("BackwardLineButton,ThumbTrack,ForwardLineButton");
            this._fpSpread.VerticalScrollBar.Name = "";
            flatScrollBarRenderer4.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(255)))), ((int)(((byte)(255)))), ((int)(((byte)(255)))));
            this._fpSpread.VerticalScrollBar.Renderer = flatScrollBarRenderer4;
            // 
            // _fpSpread_Sheet1
            // 
            this._fpSpread_Sheet1.Reset();
            this._fpSpread_Sheet1.SheetName = "Sheet1";
            // Formulas and custom names must be loaded with R1C1 reference style
            this._fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this._fpSpread_Sheet1.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.ColumnFooter.DefaultStyle.Parent = "ColumnFooterFlatOffice2016White";
            this._fpSpread_Sheet1.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.ColumnFooterSheetCornerStyle.Parent = "CornerFooterFlatOffice2016White";
            this._fpSpread_Sheet1.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.ColumnHeader.DefaultStyle.Parent = "ColumnHeaderFlatOffice2016White";
            this._fpSpread_Sheet1.FilterBar.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.FilterBar.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.FilterBar.DefaultStyle.Parent = "FilterBarFlatOffice2016White";
            this._fpSpread_Sheet1.FilterBarHeaderStyle.BackColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.FilterBarHeaderStyle.ForeColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.FilterBarHeaderStyle.Parent = "FilterBarHeaderFlatOffice2016White";
            this._fpSpread_Sheet1.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.RowHeader.DefaultStyle.Parent = "RowHeaderFlatOffice2016White";
            this._fpSpread_Sheet1.SheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this._fpSpread_Sheet1.SheetCornerStyle.Parent = "CornerHeaderFlatOffice2016White";
            this._fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // _labelCustomer
            // 
            this._labelCustomer.AutoSize = true;
            this._labelCustomer.Location = new System.Drawing.Point(6, 19);
            this._labelCustomer.Name = "_labelCustomer";
            this._labelCustomer.Size = new System.Drawing.Size(87, 13);
            this._labelCustomer.TabIndex = 0;
            this._labelCustomer.Text = "Select Customer:";
            // 
            // _comboCustomer
            // 
            this._comboCustomer.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._comboCustomer.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
            this._comboCustomer.FormattingEnabled = true;
            this._comboCustomer.Location = new System.Drawing.Point(9, 36);
            this._comboCustomer.Name = "_comboCustomer";
            this._comboCustomer.Size = new System.Drawing.Size(133, 21);
            this._comboCustomer.TabIndex = 1;
            // 
            // _labelInvoice
            // 
            this._labelInvoice.AutoSize = true;
            this._labelInvoice.Location = new System.Drawing.Point(9, 71);
            this._labelInvoice.Name = "_labelInvoice";
            this._labelInvoice.Size = new System.Drawing.Size(78, 13);
            this._labelInvoice.TabIndex = 2;
            this._labelInvoice.Text = "Select Invoice:";
            // 
            // _invoiceListBox
            // 
            this._invoiceListBox.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._invoiceListBox.FormattingEnabled = true;
            this._invoiceListBox.Location = new System.Drawing.Point(9, 95);
            this._invoiceListBox.Name = "_invoiceListBox";
            this._invoiceListBox.Size = new System.Drawing.Size(133, 319);
            this._invoiceListBox.TabIndex = 3;
            // 
            // _btnOK
            // 
            this._btnOK.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._btnOK.Location = new System.Drawing.Point(33, 426);
            this._btnOK.Name = "_btnOK";
            this._btnOK.Size = new System.Drawing.Size(85, 27);
            this._btnOK.TabIndex = 4;
            this._btnOK.Text = "OK";
            this._btnOK.UseVisualStyleBackColor = true;
            // 
            // InvoiceControl
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "InvoiceControl";
            this.pnlMain.ResumeLayout(false);
            this.pnlControls.ResumeLayout(false);
            this.pnlControls.PerformLayout();
            ((System.ComponentModel.ISupportInitialize)(this._fpSpread)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this._fpSpread_Sheet1)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread _fpSpread;
        private FarPoint.Win.Spread.SheetView _fpSpread_Sheet1;
        private System.Windows.Forms.Label _labelInvoice;
        private System.Windows.Forms.ComboBox _comboCustomer;
        private System.Windows.Forms.Label _labelCustomer;
        private System.Windows.Forms.Button _btnOK;
        private System.Windows.Forms.CheckedListBox _invoiceListBox;
    }
}
